package com.dhcc.DRGsHosp.application.impl;

import com.dhcc.DRGsHosp.application.MonthAnalysisReportApplication;
import com.dhcc.DRGsHosp.infra.util.Arith;
import com.dhcc.DRGsHosp.infra.util.MonitorResultUtil;
import com.dhcc.DRGsHosp.infra.util.StringUtils;
import org.dayatang.querychannel.JdbcTemplateObj;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.annotation.Transactional;

import javax.inject.Named;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 
 * ClassName: AnalysisDRGsReportApplicationImpl  
 * Description ：简版新版分析报告导出-POI技术   
 * Reason: TODO ADD REASON(可选).   
 * date: 2017年11月7日 下午4:37:30  
 * @author ShaozhiYuan
 * @version V1.0 DHC
 */
@Named
@Transactional
public class MonthAnalysisReportApplicationImpl implements MonthAnalysisReportApplication {

	private static final Logger LOGGER = LoggerFactory.getLogger(MonthAnalysisReportApplicationImpl.class);


	@Autowired
    private JdbcTemplateObj jdbcTemplateObj;
    @Autowired
	private NamedParameterJdbcTemplate jdbcTempSeverice;
    
	/**
	 * 
	 * @Description:获取当前系统中数据年份
	 * @return
	 */
	@Override
	public  List<Map<String,Object>> getDataYear() {
		StringBuilder sql = new StringBuilder();
		sql.append(" select to_char(a.p00290000,'yyyy')year from drgs.mr_ipinfo a where a.p00290000 is not null \n ");
		sql.append(" group by to_char(a.p00290000,'yyyy') order by to_char(a.p00290000,'yyyy') desc \n ");	
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}

	/**
	 *
	 * TODO 看日期区间内是否有数据  
	 *
	 * @author ShaoZhiYuan
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARDataIsNull(java.lang.String, java.lang.String, java.lang.String)
	 */
	@Override
	public int findARDataIsNull(String dataFrom, String dataTo, String groupScheme, String hospCode) {
		StringBuffer sql= new StringBuffer();
		sql.append(" SELECT COUNT(p.PARID) FROM drgs.MR_IPINFO mr LEFT JOIN DRGS.MR_BASEINFO  BA ON BA.PMAININDEX=mr.PMAININDEX  RIGHT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID  \n");
		sql.append(" WHERE instr('"+hospCode+"',BA.p00010001)>0  "+Arith.mrAROutDateSql(dataFrom,dataTo,"mr" )+" \n");
		sql.append(" and p.ver_region='"+groupScheme+"'\n");
		return jdbcTemplateObj.queryForObject(sql.toString(), Integer.class);
	}
	/**
	 *
	 * TODO 校验数据完整性
	 *
	 * @author ShaoZhiYuan
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARCheckedData(String, String, String, String)
	 */
	@Override
	public  List<Map<String,Object>>  findARCheckedData(String dateFrom, String dateTo, String hospCode) {
		StringBuilder sql = new StringBuilder();
		sql.append(" SELECT decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0301 is NOT null AND op.POTYPE='M' THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) FIRST_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN op.PO0101 is NOT null AND op.POTYPE='M'  THEN 1 else 0 end)/SUM(CASE WHEN op.POTYPE='M' THEN 1 else 0 end)) TWO_JUDGE_VALUE , \n");
		sql.append(" decode(COUNT(op.PO0101),0,0,SUM(CASE WHEN op.PO0301 is not null  THEN 1 else 0 end)/COUNT(op.PO0101)) THREE_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PDTYPE is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FOUR_JUDGE_VALUE, \n");
		sql.append(" decode(SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end),0,0,SUM(CASE WHEN di.PD0101 is NOT null AND di.PDTYPE ='M' THEN 1 else 0 end)/SUM(CASE WHEN di.PDTYPE ='M' THEN 1 else 0 end)) FIVE_JUDGE_VALUE\n");
		sql.append(" from DRGS.MR_BASEINFO a left join drgs.MR_IPINFO mr on a.PMAININDEX = mr.PMAININDEX \n");
		sql.append(" LEFT JOIN drgs.mr_operation op on mr.PMAININDEX=op.PMAININDEX \n");
		sql.append(" LEFT  JOIN  drgs.MR_DIAGNOSE  di on mr.PMAININDEX=di.PMAININDEX\n");
		sql.append(" where instr('"+hospCode+"',a.p00010001)>0  "+ Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+"  \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	/**
	 *
	 * TODO 获取手术病例
	 *
	 * @author ShaoZhiYuan
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findHospCaseOperationCount(Map)
	 */
	@Override
	public String findHospCaseOperationCount(Map<String, Object> map) {
		String dateFrom = map == null?"":map.get("dateFrom").toString();
		String dateTo = map == null?"":map.get("dateTo").toString();
		String hospCode = map == null?"":map.get("hospCode").toString();
		String sqlResult = "SELECT COUNT(A.PMAININDEX) FROM DRGS.MR_BASEINFO a left join DRGS.MR_IPINFO B on a.PMAININDEX = b.PMAININDEX JOIN DRGS.MR_OPERATION o ON o.PMAININDEX = B.PMAININDEX WHERE o.PO1400 ='1'  and  o.POTYPE='M' "+ Arith.mrAROutDateSql(dateFrom,dateTo,"B") +" and instr('"+hospCode+"',a.p00010001)>0 ";
		String caseOperationCount = null;
		try {
			caseOperationCount = jdbcTemplateObj.queryForObject(sqlResult.toString(), String.class);
		} catch (DataAccessException e) {
			LOGGER.error(sqlResult);
			e.printStackTrace();
		}
		return caseOperationCount;
	}
	
	@Override
	public List<Map<String, Object>> executeQueryForList(String sql,Map<String,Object> condition) {
		List<Map<String, Object>> mapList = null;
		try {
			mapList = jdbcTempSeverice.queryForList(sql, condition) ;
		} catch (Exception e) {
			mapList = new ArrayList<>() ;
			e.printStackTrace();
		}
		return mapList;
	}
 
	@Override
	public Map<String, Object> executeQueryForMap(String sql,Map<String,Object> condition) {
		Map<String, Object> resultMap = null ;
		try {
			resultMap = jdbcTempSeverice.queryForMap(sql, condition);
		} catch (Exception e) {
			resultMap = new HashMap<>() ;
			e.printStackTrace();
		}
		return resultMap ;
	}
	
	@Override
	public List<Map<String, Object>> quaterInCaseRatio(String dateFrom,String dateTo,String depCode,String hospCode) {
		String sql="";
		sql+=" select timeDimention,case when TotalCaseCnt<>0 then InCaseCnt/TotalCaseCnt * 100  else 0 end InCaseRatio  ";
		sql+=" from(select to_char(ipinfo.p00290000,'yyyy')||'年'||to_char(ipinfo.p00290000,'q')||'季度' timeDimention, ";
		sql+=" sum(case when b.pardrg_code is not null and ipinfo.p00320000 < 61 then 1 else 0 end) as InCaseCnt," ;
		sql+=" sum(case when ipinfo.p00320000 < 61 then 1 else 0 end) as TotalCaseCnt " ;
		sql+=" from DRGs.Mr_Baseinfo a left join drgs.mr_ipinfo ipinfo on a.pmainindex = ipinfo.pmainindex ";
		sql+=" LEFT Join drgs.t_department dep on dep.dep_code=ipinfo.p00300001 ";
		sql+=" left join DRGs.t_patientdrg b on a.pmainindex=b.parid where instr('"+hospCode+"',a.p00010001)>0 "+ Arith.mrAROutDateSql(dateFrom,dateTo,"ipinfo")+" ";
		if(StringUtils.hasLength(depCode)) {
			sql+= " and dep.DEP_CODE is not null and dep.DEP_CODE= '"+depCode+"' \n" ;
		}
		sql+=" group by to_char(ipinfo.p00290000,'yyyy')||'年'||to_char(ipinfo.p00290000,'q')||'季度' ) order by timeDimention  ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	
	@Override
	public List<Map<String, Object>> findNotInGroupReasonList(Map<String, Object> map) {
		String dateFrom = map.get("dateFrom") == null?"":map.get("dateFrom").toString();
		String dateTo = map.get("dateTo") == null?"":map.get("dateTo").toString();
		String ver_region = map.get("ver_region") == null?"":map.get("ver_region").toString();
		String hospCode = map.get("hospCode") == null?"":map.get("hospCode").toString(); //院区编码
		String depCode = MonitorResultUtil.object2String(map.get("depCode"));
		String sql="";
		sql+=" SELECT g.NOTIN_REASON REASON ,d.dic_desc DECC, d.dic_resume RESUME , COUNT(G.MR_ADM) CNT FROM DRGs.Mr_Baseinfo a left join drgs.mr_ipinfo mr on a.pmainindex = mr.pmainindex ";
		sql+=" LEFT JOIN drgs.t_groupinglog g ON mr.PMAININDEX=g.MR_ADM \n ";
		sql+=" LEFT JOIN drgs.t_patientdrg p ON mr.PMAININDEX=p.PARID ";
		sql+=" LEFT JOIN drgs.t_department dep ON dep.dep_code= mr.p00300001 \n ";
		sql+=" left join drgs.mr_dictionarydata d on d.dic_value=g.notin_reason and d.dic_code='NOTIN_REASON' ";
		sql+=" where instr('"+hospCode+"',a.p00010001)>0 "+ Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" and  mr.P00320000 <61 and p.pardrg_code is null ";
		if(StringUtils.hasLength(depCode)) {
			sql+= " and dep.DEP_CODE is not null and dep.DEP_CODE= '"+depCode+"' \n" ;
		}
		sql+=" and p.ver_region='"+ver_region+"' AND  g.NOTIN_REASON not in ('0') GROUP BY g.NOTIN_REASON ,d.dic_desc, d.dic_resume order by cnt desc ";
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ; 
		return list;
	}
	
	/**
	 * 
	 * TODO 获取医院基本信息
	 * 
	 * @author ShaoZhiYuan  
	 * @see com.dhcc.DRGsHosp.application.AnalysisDRGsReportApplication#findARSimpHospBaseQuota()
	 */
	@Override
	public List<Map<String, Object>> findARSimpHospBaseQuota(Map<String, Object> map) {
		String dateFrom = map.get("dateFrom") == null?"":map.get("dateFrom").toString();
		String dateTo = map.get("dateTo") == null?"":map.get("dateTo").toString();
		String ver_region = map.get("ver_region") == null?"":map.get("ver_region").toString();
		String hospCode = map.get("hospCode") == null?"":map.get("hospCode").toString(); //院区编码
		StringBuffer sql= new StringBuffer();
		sql.append(" select X.allcnt ALLCNT ,X.ingroup INGROUP,X.over60 OVERSIXTY,ROUND(decode(X.allcnt-X.over60,0,0,X.ingroup*100/(X.allcnt-X.over60)),2) GROUPRATIO ,X.notingroup NOTINGROUP \n");
		sql.append(" FROM ( select COUNT(mr.PMAININDEX) allcnt,SUM(CASE WHEN mr.P00320000 > 60 THEN 1 else 0 end  ) over60, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is not NULL and p.ver_region='"+ver_region+"' THEN 1 else 0 END ) ingroup, \n");
		sql.append(" sum(CASE WHEN mr.P00320000 <61 AND p.PARDRG_CODE is  NULL and p.ver_region='"+ver_region+"'  THEN 1 else 0 END ) notingroup \n");
		sql.append(" from DRGs.Mr_Baseinfo a left join drgs.mr_ipinfo mr on a.pmainindex = mr.pmainindex  left join drgs.t_patientdrg p on mr.PMAININDEX= p.PARID \n");
		sql.append(" where instr('"+hospCode+"',a.p00010001)>0 "+ Arith.mrAROutDateSql(dateFrom,dateTo,"mr")+" ) X \n");
		List<Map<String,Object>> list =jdbcTemplateObj.queryForList(sql.toString()) ;
		return list;
	}
	
	
}
